{smcl}
{com}{sf}{ul off}{txt}{.-}
      name:  {res}<unnamed>
       {txt}log:  {res}C:\Users\hilar\Box\lights_2022\replication package\code\merge_grid.smcl
  {txt}log type:  {res}smcl
 {txt}opened on:  {res}13 Dec 2025, 17:41:55
{txt}
{com}. timer on 1
{txt}
{com}. 
. use "../data/grid_lights_drought.dta", clear
{txt}
{com}. sort x y year
{txt}
{com}. drop if missing(lights)
{txt}(0 observations deleted)

{com}. 
. * merge cross-sectional data that's at .05 degree cell level
. merge m:1 x y using "../data/flares.dta"
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}      58,436,271
{txt}{col 9}from master{col 30}{res}      58,432,836{txt}  (_merge==1)
{col 9}from using{col 30}{res}           3,435{txt}  (_merge==2)

{col 5}Matched{col 30}{res}         140,916{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop if _merge==2
{txt}(3,435 observations deleted)

{com}. gen flare=(detection_frequency_2012>0) & _merge==3
{txt}
{com}. drop _merge
{txt}
{com}. summarize lights if flare==1, detail

                           {txt}lights
{hline 61}
      Percentiles      Smallest
 1%    {res}        0              0
{txt} 5%    {res}        0              0
{txt}10%    {res}        0              0       {txt}Obs         {res}     83,268
{txt}25%    {res} .1111111              0       {txt}Sum of wgt. {res}     83,268

{txt}50%    {res}        6                      {txt}Mean          {res} 12.67336
                        {txt}Largest       Std. dev.     {res} 15.98655
{txt}75%    {res} 18.97222             63
{txt}90%    {res} 39.11111             63       {txt}Variance      {res} 255.5698
{txt}95%    {res} 49.83333             63       {txt}Skewness      {res} 1.427633
{txt}99%    {res} 60.52778             63       {txt}Kurtosis      {res} 4.099532
{txt}
{com}. 
. merge m:1 x y using "../data/urban.dta"
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}       5,403,133
{txt}{col 9}from master{col 30}{res}       4,091,388{txt}  (_merge==1)
{col 9}from using{col 30}{res}       1,311,745{txt}  (_merge==2)

{col 5}Matched{col 30}{res}      54,482,364{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop if _merge==2
{txt}(1,311,745 observations deleted)

{com}. drop _merge
{txt}
{com}. 
. 
. *get pfaf4 codes, 
. merge m:1 x y using  "../data/pfaf4_grid.dta"
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}       4,498,414
{txt}{col 9}from master{col 30}{res}       3,552,240{txt}  (_merge==1)
{col 9}from using{col 30}{res}         946,174{txt}  (_merge==2)

{col 5}Matched{col 30}{res}      55,021,512{txt}  (_merge==3)
{col 5}{hline 41}

{com}. summarize x y if _merge==2, detail

                              {txt}x
{hline 61}
      Percentiles      Smallest
 1%    {res}      811              1
{txt} 5%    {res}     1424              1
{txt}10%    {res}     1839              1       {txt}Obs         {res}    946,174
{txt}25%    {res}     3435              1       {txt}Sum of wgt. {res}    946,174

{txt}50%    {res}     4065                      {txt}Mean          {res} 3867.841
                        {txt}Largest       Std. dev.     {res} 1281.712
{txt}75%    {res}     4710           7200
{txt}90%    {res}     5400           7200       {txt}Variance      {res}  1642785
{txt}95%    {res}     5620           7200       {txt}Skewness      {res} -.606774
{txt}99%    {res}     6255           7200       {txt}Kurtosis      {res} 2.745111

                              {txt}y
{hline 61}
      Percentiles      Smallest
 1%    {res}       19              1
{txt} 5%    {res}       67              1
{txt}10%    {res}      147              1       {txt}Obs         {res}    946,174
{txt}25%    {res}      637              1       {txt}Sum of wgt. {res}    946,174

{txt}50%    {res}      907                      {txt}Mean          {res} 840.1717
                        {txt}Largest       Std. dev.     {res} 433.6292
{txt}75%    {res}     1070           2682
{txt}90%    {res}     1180           2682       {txt}Variance      {res} 188034.3
{txt}95%    {res}     1537           2690       {txt}Skewness      {res} .2658801
{txt}99%    {res}     2086           2690       {txt}Kurtosis      {res} 4.220826
{txt}
{com}. 
. sort conpfaf4 year
{txt}
{com}. 
. preserve
{txt}
{com}. 
. * aggregate to pfaf4 levels as needed for upstream drought 
. drop if flare
{txt}(1,029,442 observations deleted)

{com}. collapse (mean) lightspfaf4=lights droughtpfaf4=drought (count) cellsinpfaf4=lights, by(conpfaf4 year)
{res}{txt}
{com}. 
. label var lightspfaf4 "Average lights by pfaf4 & year"
{txt}
{com}. label var droughtpfaf4 "Average drought by pfaf4 & year"
{txt}
{com}. label var cellsinpfaf4 "Num .05 degree cells in pfaf4"
{txt}
{com}. 
. label data "Drought and lights by pfaf4 year" 
{txt}
{com}. 
. tempfile pfaf4_drought
{txt}
{com}. 
. save `pfaf4_drought'
{txt}{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_70c8_000002.tmp{rm}
saved
as .dta format
{p_end}

{com}. 
. 
. *create information on lights by pfaf4 so can exclude unpopulated places
. collapse (min) minlights=lightspfaf4, by(conpfaf4)
{res}{txt}
{com}.  
. label var minlights "Min (over time) lights for subbasin" 
{txt}
{com}. 
. 
. save minlights, replace
{txt}{p 0 4 2}
file {bf}
minlights.dta{rm}
saved
{p_end}

{com}. restore
{txt}
{com}. 
. * pick Pfaf4 that is most common in .5 degree grid cell
. 
. gen x_10=floor((x-1)/10) + 1
{txt}
{com}. gen y_10=floor((y-1)/10) + 1 
{txt}
{com}. 
. bys x_10 y_10: egen conpfaf4mode=mode(conpfaf4), minmode 
{p 0 9 2}{txt}
warning: For at least one group, conpfaf4 contains all 
missing values. Generating missing values for the modes in these
groups. Use option {bf:missing} to treat missing values as a
category.
{p_end}
(3,375,300 missing values generated)

{com}. 
. *now collapse to .5 degree
. collapse (mean) lights drought ihslights urbanshare (sum) flare urban (firstnm) conpfaf4mode, by(x_10 y_10 year)
{res}{txt}
{com}. 
. 
. rename x_10 x
{res}{txt}
{com}. rename y_10 y
{res}{txt}
{com}. rename conpfaf4mode conpfaf4
{res}{txt}
{com}. replace urban=(urban>0)
{txt}(162,732 real changes made)

{com}. 
. 
. label var flare "Flare in .5 deg cell in 2012"
{txt}
{com}. label var lights "Light index"
{txt}
{com}. label var drought "Remote-sensed DSI"
{txt}
{com}. label var conpfaf4 "Pfaf4, unique by continent"
{txt}
{com}. label var urban "Some urban area in cell"
{txt}
{com}. label var urbanshare "Urban share"
{txt}
{com}. 
. sort x y year
{txt}
{com}. 
. 
. * add temp and precip data at half degree level
. merge 1:1 x y year using "../data/annual_av_tmp.dta"
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}       1,664,457
{txt}{col 9}from master{col 30}{res}          26,337{txt}  (_merge==1)
{col 9}from using{col 30}{res}       1,638,120{txt}  (_merge==2)

{col 5}Matched{col 30}{res}         694,680{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop if _merge==2
{txt}(1,638,120 observations deleted)

{com}. drop _merge
{txt}
{com}. 
. merge 1:1 x y year using "../data/annual_av_pre.dta"
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}       1,664,457
{txt}{col 9}from master{col 30}{res}          26,337{txt}  (_merge==1)
{col 9}from using{col 30}{res}       1,638,120{txt}  (_merge==2)

{col 5}Matched{col 30}{res}         694,680{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop if _merge==2
{txt}(1,638,120 observations deleted)

{com}. drop _merge
{txt}
{com}. 
. 
. sort conpfaf4
{txt}
{com}. 
. merge m:1 conpfaf4 using "../data/pfaf4_char.dta"
{res}{txt}{p 0 7 2}
(variable
{bf:conpfaf4} was {bf:float}, now {bf:double} to accommodate using data's values)
{p_end}

{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}          43,976
{txt}{col 9}from master{col 30}{res}          40,860{txt}  (_merge==1)
{col 9}from using{col 30}{res}           3,116{txt}  (_merge==2)

{col 5}Matched{col 30}{res}         680,157{txt}  (_merge==3)
{col 5}{hline 41}

{com}. 
. drop if _merge==2
{txt}(3,116 observations deleted)

{com}. 
. drop _merge
{txt}
{com}. 
. 
. merge m:1 conpfaf4 using minlights
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}           5,016
{txt}{col 9}from master{col 30}{res}           2,221{txt}  (_merge==1)
{col 9}from using{col 30}{res}           2,795{txt}  (_merge==2)

{col 5}Matched{col 30}{res}         718,796{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop if _merge==2
{txt}(2,795 observations deleted)

{com}. drop _merge
{txt}
{com}. 
. drop if minlights==0 | missing(minlights)
{txt}(186,914 observations deleted)

{com}. 
. sort x y year
{txt}
{com}. 
. *11 bins
. generate dsi_cat_temp=recode(drought,-1.5,-1.2,-.9,-.6,-.3,.3,.6,.9,1.2,1.5,4) if !missing(drought)
{txt}(17,431 missing values generated)

{com}. egen byte dsi_cat11=group(dsi_cat_temp) if !missing(drought)
{txt}(17,431 missing values generated)

{com}. drop dsi_cat_temp
{txt}
{com}. *7 bins
. gen dsi_cat7=dsi_cat11
{txt}(17,431 missing values generated)

{com}. replace dsi_cat7=7 if dsi_cat11>=7
{txt}(145,827 real changes made)

{com}. 
. ** binary
. gen extreme=(dsi_cat11==1) if !missing(dsi_cat11)
{txt}(17,431 missing values generated)

{com}. gen extsev=(dsi_cat11<=2) if !missing(dsi_cat11)
{txt}(17,431 missing values generated)

{com}. gen byte esmdsi=(dsi_cat11<=3) if !missing(dsi_cat11)
{txt}(17,431 missing values generated)

{com}. 
. 
. 
. label define cat11 1 "Extreme drought"  2 "Severe drought" 3 "Moderate drought" 4 "Mild drought" ///
> 5 "Incipient drought" 6 "Near normal" 7 "Incipient wet spell"  8 "Slightly wet" 9 "Moderately wet" ///
> 10 "Very wet"  11 "Extremely wet"
{txt}
{com}. label values dsi_cat11 cat11
{txt}
{com}. label define cat7 1 "Extreme drought"  2 "Severe drought" 3 "Moderate drought" 4 "Mild drought" ///
> 5 "Incipient drought" 6 "Near normal" 7 "Wet"  
{txt}
{com}. label values dsi_cat7 cat7
{txt}
{com}. label var dsi_cat11 "DSI, 11 categories"
{txt}
{com}. label var dsi_cat7 "DSI, 7 categories"
{txt}
{com}. label var extsev "Extreme or severe drought (DSI)"
{txt}
{com}. label var esmdsi "Moderate or worse drought (DSI)"
{txt}
{com}. 
. sort y x year
{txt}
{com}. 
. ** merge sc-PDSI data, also only at half degree level
. merge 1:1 y x year using "../data/pdsi.dta", sorted
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}       2,805,559
{txt}{col 9}from master{col 30}{res}          17,431{txt}  (_merge==1)
{col 9}from using{col 30}{res}       2,788,128{txt}  (_merge==2)

{col 5}Matched{col 30}{res}         516,672{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop if _merge==2
{txt}(2,788,128 observations deleted)

{com}. drop _merge
{txt}
{com}. 
. *using definitions in van der Schrier article
. generate pdsi_cat_temp=recode(pdsi_av,-4,-3,-2,-1,-.5,.5,1,2,3,4,30) if !missing(pdsi_av)
{txt}(51,475 missing values generated)

{com}. gen pdsi_summer_cat_temp=recode(pdsi_summer,-4,-3,-2,-1,-.5,.5,1,2,3,4,30) if !missing(pdsi_summer)
{txt}(51,475 missing values generated)

{com}. gen pdsi_min_cat_temp=recode(pdsi_min,-4,-3,-2,-1,-.5,.5,1,2,3,4,30) if !missing(pdsi_min)
{txt}(51,475 missing values generated)

{com}. 
. *matching distribution of cut points in remote sensed DSI
. *_pctile pdsi_av, percentiles(4, 8, 15, 25, 36, 63, 75, 85, 92, 97)
. *generate pdsi_cat_temp=recode(pdsi_av,`r(r1)',`r(r2)',`r(r3)',`r(r4)',`r(r5)',`r(r6)',`r(r7)',`r(r8)',`r(r9)',`r(r10)',30)
. egen byte pdsi_cat=group(pdsi_cat_temp)
{txt}(51,475 missing values generated)

{com}. egen byte pdsi_summer_cat=group(pdsi_summer_cat_temp)
{txt}(51,475 missing values generated)

{com}. egen byte pdsi_min_cat=group(pdsi_min_cat_temp)
{txt}(51,475 missing values generated)

{com}. 
. drop pdsi_cat_*temp 
{txt}
{com}. 
. label var pdsi_cat "sc-PDSI categories"
{txt}
{com}. label var pdsi_summer_cat "sc-PDSI categories in June or Dec"
{txt}
{com}. label var pdsi_min_cat "sc-PDSI categories at annual min"
{txt}
{com}. 
. label define pcat 1 "Extremely dry"  2 "Severely dry" 3 "Moderately dry" 4 "Mildly dry" ///
> 5 "Incipient drought" 6 "Near normal" 7 "Incipient wet spell"  8 "Slightly wet" 9 "Moderately wet" ///
> 10 "Very wet"  11 "Extremely wet"
{txt}
{com}. label values pdsi_cat pcat
{txt}
{com}. label values pdsi_summer_cat pcat
{txt}
{com}. label values pdsi_min_cat pcat
{txt}
{com}. 
. 
. 
. gen byte extpdsi=(pdsi_cat==1) if !missing(pdsi_cat)
{txt}(51,475 missing values generated)

{com}. gen byte extsevpdsi=(pdsi_cat<=2) if !missing(pdsi_cat)
{txt}(51,475 missing values generated)

{com}. gen byte esmpdsi=(pdsi_cat<=3) if !missing(pdsi_cat)
{txt}(51,475 missing values generated)

{com}. 
. label variable extpdsi "Extreme drought (scPDSI)" 
{txt}
{com}. label variable extsevpdsi "Extreme or severe drought (scPDSI)" 
{txt}
{com}. label variable esmpdsi "Moderate or worse drought (scPDSI)"
{txt}
{com}. 
. 
. *merge on half degree grids
. * read in read_other_raster.do
. 
. merge m:1 y x using "../data/cropland2000.dta"
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}         149,086
{txt}{col 9}from master{col 30}{res}               0{txt}  (_merge==1)
{col 9}from using{col 30}{res}         149,086{txt}  (_merge==2)

{col 5}Matched{col 30}{res}         534,103{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop if _merge==2
{txt}(149,086 observations deleted)

{com}. drop _merge
{txt}
{com}. 
. merge m:1 y x using "../data/irrigation.dta"
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}         149,086
{txt}{col 9}from master{col 30}{res}               0{txt}  (_merge==1)
{col 9}from using{col 30}{res}         149,086{txt}  (_merge==2)

{col 5}Matched{col 30}{res}         534,103{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop if _merge==2
{txt}(149,086 observations deleted)

{com}. drop _merge
{txt}
{com}. 
. * use WB definition of center of cells
. gen lon=-179.75+.5*(x-1)
{txt}
{com}. gen lat=74.75-.5*(y-1)
{txt}
{com}. 
. label var lon "Logitude (midpoint)"
{txt}
{com}. label var lat "Latitude (midpoint)"
{txt}
{com}. 
. sort lon lat
{txt}
{com}. 
. *WB groundater grid
. merge m:1 lon lat using "../data/aqtyp_gwresource_grid05deg.dta"
{res}{txt}{p 0 7 2}
(variable
{bf:lat} was {bf:float}, now {bf:double} to accommodate using data's values)
{p_end}
{p 0 7 2}
(variable
{bf:lon} was {bf:float}, now {bf:double} to accommodate using data's values)
{p_end}

{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}          25,464
{txt}{col 9}from master{col 30}{res}             547{txt}  (_merge==1)
{col 9}from using{col 30}{res}          24,917{txt}  (_merge==2)

{col 5}Matched{col 30}{res}         533,556{txt}  (_merge==3)
{col 5}{hline 41}

{com}. 
. drop if _merge==2
{txt}(24,917 observations deleted)

{com}. drop _merge
{txt}
{com}. 
. 
. label var grid_id "WB cell ID"
{txt}
{com}. label var aqtyp_max "Aquifer typology class with highest % grid cell (excluding NA)"
{txt}
{com}. label var aqtyp_pct_ma "Major alluvial (%)" 
{txt}
{com}. label var aqtyp_pct_cx "Complex aquifer (%)" 
{txt}
{com}. label var aqtyp_pct_kt  "Karstic (%)"
{txt}
{com}. label var aqtyp_pct_ls "Local/shallow aquifer (%)"
{txt}
{com}. label var aqtyp_pct_NA "No aquifer data (%)" 
{txt}
{com}. label var resource_pct_grid_na "Not covered by country-lithological-outcrop resource data (% of grid cell)"
{txt}
{com}. label var resource "GW resource, sum w/in cell (10^9 m3/yr)" 
{txt}
{com}. label var resource_norm "GW resource, normalized (10^9 m3/yr)"
{txt}
{com}. 
. drop grid* pfaf4
{txt}
{com}. 
. 
. 
. 
. 
. *** for upstream drought analysis, get basin level drought,
. 
. sort conpfaf4 year
{txt}
{com}. 
. merge m:1 conpfaf4 year using `pfaf4_drought'
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}          98,995
{txt}{col 9}from master{col 30}{res}          17,431{txt}  (_merge==1)
{col 9}from using{col 30}{res}          81,564{txt}  (_merge==2)

{col 5}Matched{col 30}{res}         516,672{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop if _merge==2
{txt}(81,564 observations deleted)

{com}. drop lightspfaf4 cellsinpfaf4 _merge
{txt}
{com}. rename droughtpfaf4 d4temp
{res}{txt}
{com}. 
. tempfile all
{txt}
{com}. save `all'
{txt}{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_70c8_000003.tmp{rm}
saved
as .dta format
{p_end}

{com}. 
. 
. ** add upstream drought information
. use "../data/upstream_basins.dta", clear
{txt}
{com}. rename conpfaf4 start_basin
{res}{txt}
{com}. rename up conpfaf4
{res}{txt}
{com}. 
. sort conpfaf4
{txt}
{com}. 
. *merge pfaf4-drought data created above
. *want all years and all upstream basins
. joinby conpfaf4 using `pfaf4_drought'
{txt}
{com}. 
. collapse (mean) upstream_drought=droughtpfaf4, by(start_basin year)
{res}{txt}
{com}. 
. rename start_basin conpfaf4
{res}{txt}
{com}. 
. label var upstream_drought "Average drought for all upstream subbasins"
{txt}
{com}. 
. sort conpfaf4 year
{txt}
{com}. 
. merge 1:m conpfaf4 year using `all'
{res}{txt}{p 0 7 2}
(variable
{bf:conpfaf4} was {bf:long}, now {bf:double} to accommodate using data's values)
{p_end}

{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}         472,159
{txt}{col 9}from master{col 30}{res}          40,416{txt}  (_merge==1)
{col 9}from using{col 30}{res}         431,743{txt}  (_merge==2)

{col 5}Matched{col 30}{res}         102,360{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop if _merge==1
{txt}(40,416 observations deleted)

{com}. 
. drop _merge
{txt}
{com}. rename d4temp droughtpfaf4
{res}{txt}
{com}. 
. compress
  {txt}variable {bf}{res}x{sf}{txt} was {bf}{res}float{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}{res}y{sf}{txt} was {bf}{res}float{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}{res}dsi_cat7{sf}{txt} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}extreme{sf}{txt} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}extsev{sf}{txt} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}conpfaf4{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}long{sf}
  {txt}variable {bf}{res}flare{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}urban{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}byte{sf}
{txt}  (16,557,193 bytes saved)

{com}. 
. save for_reg, replace
{txt}{p 0 4 2}
(file {bf}
for_reg.dta{rm}
not found)
{p_end}
{p 0 4 2}
file {bf}
for_reg.dta{rm}
saved
{p_end}

{com}. 
. timer off 1
{txt}
{com}. timer list 1
{res}   1:    181.30 /        1 =     181.2990
{txt}
{com}. log close
      {txt}name:  {res}<unnamed>
       {txt}log:  {res}C:\Users\hilar\Box\lights_2022\replication package\code\merge_grid.smcl
  {txt}log type:  {res}smcl
 {txt}closed on:  {res}13 Dec 2025, 17:44:56
{txt}{.-}
{smcl}
{txt}{sf}{ul off}